package test;

import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberInputStream;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.UUID;

public class InsertIMEI {

	/**
	 * @param args
	 * @throws SQLException 
	 * @throws IOException 
	 */
	public static void main(String[] args) throws SQLException, IOException {
		Connection conn = DriverManager.getConnection("jdbc:mysql://www.smart-tracking.com:9999/gpsMap?characterEncoding=utf-8", "cong", "29749716");
		Statement stmt = conn.createStatement();
        
		ResultSet rs = stmt.executeQuery("select userid from user where name='cong'");
		rs.next();
		int congId = rs.getInt("userid");
		rs.close();
		LineNumberReader reader = new LineNumberReader(new FileReader("E:/map/imei2.txt")); 
		
		String line = null;
		int count = 0;
		while((line = reader.readLine()) != null){
			String[] imeis = line.split("\\s+");
			for(String imei : imeis){
				count++;
				int start = imei.indexOf("r_")+2;
				int end = imei.indexOf(".frm");
				System.out.println("imei="+imei+",count="+count+", number="+imei.substring(start, end));
				imei = imei.substring(start, end);
				String sql = "select t.trackerid,t.trackername,u.name,u.realname,u.email" +
						" from tracker t left join user u on t.userid=u.userid where userTrackerid='"+imei+"'";
				
				rs = stmt.executeQuery(sql);
				
				if(rs.next()){ /// 有跟踪器
					if(rs.getString("name") == null){ // 没有所属用户的tracker,需要重新该用户
						sql = "update tracker set userid="+congId+" where usertrackerid='"+imei+"'";
						System.out.println("lose user, sql="+sql);
						stmt.executeUpdate(sql);
						rs.close();
					}
					rs.close();
					continue;
				}
				// 没有跟踪器，需要添加
				System.out.println("no tracker for imei="+imei);
				sql = "insert into tracker(trackerid,userid,trackername,createdate,carmodelid," +
				"userTrackerId,driverId,driverName,driverMobile,remark, mailEnabled,modelName) "+
		   " values('"+UUID.randomUUID().toString().replaceAll("-", "")+"','"+congId+"','"+imei+"',now(),'7','"
		   +imei+"','','','','recovery from lose',0,'')";
		
				sql = sql.replaceAll("'null'", "''");
				System.out.println("sql="+sql);
				stmt.executeUpdate(sql);
			}
		}
		
		conn.close();
//		String sql = "insert into tracker(trackerid,userid,trackername,createdate,carmodelid," +
//		"userTrackerId,driverId,driverName,driverMobile,remark, mailEnabled,modelName) "+
//   " values('"+trackerId+"','"+tracker.getUserid()+"','"+tracker.getTrackerName()+"',now(),'7','"
//   +tracker.getUserTrackerId()+"','"+tracker.getDriverID()+"','"+tracker.getDriverName()
//   +"','"+tracker.getDriverMobile()+"','"+tracker.getRemark()+"',"+(tracker.isMailEnabled() ? 1 : 0)+",'"+tracker.getModelName()+"')";
//
//sql = sql.replaceAll("'null'", "''");

	}

}
